defmodule PsqlLib do
	@moduledoc false
	use Timex
	@time_type NaiveDateTime
	@struct_field :__struct__
	@sql :sql
	@return_json :return_json

	def insert_not_exists(prefix, struct1, opts \\ []) do
		{mod, struct3} = Map.pop(struct1, @struct_field)
		params = Keyword.get(opts, :params, [])
		index = length(params) + 1
		table = get_table(prefix, mod, opts)
		{str, vals, _} = insert_values(struct1, {nil, [], index})
		{
			"""
			INSERT INTO #{table} #{insert_keys(Enum.reverse(struct3))} SELECT #{str}
			WHERE NOT EXISTS(
				SELECT 1 FROM #{table} #{opts[:not_exists]}
			)
			""",
			params ++ vals
		}
	end

	def insert(prefix, struct1, opts) when is_map(struct1) do
		{mod, struct3} = Map.pop(struct1, @struct_field)
		sql = get_return_sql(opts)
		params = Keyword.get(opts, :params, [])
		index = length(params) + 1
		{str, vals, _} = insert_values(struct1, {nil, [], index})
		{
			"INSERT INTO " <> get_table(prefix, mod, opts)
			<> insert_keys(Enum.reverse(struct3)) <> " VALUES(" <> str <> ") " <> sql,
			params ++ vals
		}
	end
	def insert(prefix, objects, opts \\ []) when is_list(objects) do
		sql = get_return_sql(opts)
		struct = hd(objects)
		is_map(struct) || PrivateCandy.debug_remind(__ENV__, "#{inspect(struct)} is not struct")
		params = Keyword.get(opts, :params, [])
		index = length(params) + 1
		{mod, struct} = Map.pop(struct, @struct_field)
		{str, vals, _} = Enum.reduce(
			objects,
			{nil, [], index},
			fn (obj, {str_acc, vals_ac, index_acc}) ->
				{str, vals_acc, index} = insert_values(obj, {nil, vals_ac, index_acc})
				{append_str(str_acc, ",", "(" <> str <> ")"), vals_acc, index}
			end
		)
		{
			"INSERT INTO " <> get_table(prefix, mod, opts)
			<> insert_keys(Enum.reverse(struct)) <> " VALUES" <> IO.iodata_to_binary(str) <> sql,
			params ++ vals
		}
	end

	@doc """
	opts:
		return_old: [string], 返回旧的
		return_new: [string], 返回新的, 只有有 return_old 这个才会生效
		sql: 里的returning只能返回更新后的数据
	"""
	def update(prefix, mod, update, where, opts \\ []) when update != [] do
		return_old = opts[:return_old]
		return_old_json = opts[:return_old_json]
		params = Keyword.get(opts, :params, [])
		index = length(params) + 1
		if return_old != nil || return_old_json != nil do
			primary_key = mod.primary_key()
			pk = if primary_key == nil do
				unique_key = opts[:unique_key]
				unique_key != nil || PrivateCandy.debug_remind(__ENV__, "#{inspect(mod)} 没有设置主键")
				if is_list(unique_key) do
					Enum.map_join(unique_key, " and ", &("x.#{&1}=y.#{&1}"))
				else
					"x.#{unique_key}=y.#{unique_key}"
				end
			else
				case primary_key do
					{k1, k2} ->
						"x.#{k1}=y.#{k1} and x.#{k2}=y.#{k2}"
					_ ->
						"x.#{primary_key}=y.#{primary_key}"
				end
			end
			table = get_table(prefix, mod, opts)
			{set_str, vals, index} = sql_equal_param(update, index)
			{where_str, where_vals, _} = sql_filter_param(mod, where, vals, index)
			return_new_json = case Keyword.get(opts, :return_new_json, []) do
				[] ->
					""
				list ->
					a = Enum.map_join(list, ",", &"'#{&1}',x.#{&1}")
					", json_build_object(#{a})"
			end
			return_new = case Keyword.get(opts, :return_new, []) do
				[] ->
					return_new_json
				v ->
					"," <> Enum.map_join(v, ",", &("x.#{&1}")) <> return_new_json
			end
			return_old = case {return_old, return_old_json} do
				{nil, _} ->
					a = Enum.map_join(return_old_json, ",", &"'#{&1}',y.#{&1}")
					"json_build_object(#{a})"
				{_, nil} ->
					Enum.map_join(return_old, ",", &("y.#{&1}"))
				{_, _} ->
					a = Enum.map_join(return_old_json, ",", &"'#{&1}',y.#{&1}")
					Enum.map_join(return_old, ",", &("y.#{&1}")) <> ",json_build_object(#{a})"
			end
			{
				"""
				UPDATE #{table} x
				SET #{set_str}
				FROM (SELECT * FROM #{table} #{where_str} FOR UPDATE) y
				WHERE #{pk}
				RETURNING #{return_old} #{return_new};
				""",
				params ++ where_vals
			}
		else
			sql = get_return_sql(opts)
			{set_str, vals, index} = sql_equal_param(update, index)
			{where_str, where_vals, _} = sql_filter_param(mod, where, vals, index)
			{
				"UPDATE " <> get_table(prefix, mod, opts) <> " SET " <> set_str <> where_str <> sql,
				params ++ where_vals
			}
		end
	end

	def query(prefix, mod, select, where, opts1 \\ []) do
		opts = Keyword.delete(opts1, @return_json)
		sql = get_return_sql(opts)
		params = Keyword.get(opts, :params, [])
		index = length(params) + 1
		{where_str, where_vals, _} = sql_filter_param(mod, where, [], index)
		select = if select == [] do
			"*"
		else
			Enum.map_join(select, ", ", &(&1))
		end
		case Keyword.get(opts1, @return_json) do
			nil ->
				{
					"SELECT " <> select <> " FROM " <> get_table(prefix, mod, opts) <> where_str <> sql,
					params ++ where_vals
				}
			_ ->
				str = "SELECT " <> select <> " FROM " <> get_table(prefix, mod, opts) <> where_str <> sql
				{
					"select array_to_json(array_agg(row_to_json(t))) from (#{str})t",
					params ++ where_vals
				}
		end
	end

	def count(prefix, mod, where, opts \\ []) do
		sql = get_return_sql(opts)
		params = Keyword.get(opts, :params, [])
		index = length(params) + 1
		{where_str, where_vals, _} = sql_filter_param(mod, where, [], index)
		{"select count(*) from #{get_table(prefix, mod, opts)} #{where_str} #{sql}", params ++ where_vals}
	end

	def delete(prefix, mod, where, opts \\ []) do
		sql = get_return_sql(opts)
		params = Keyword.get(opts, :params, [])
		index = length(params) + 1
		{where_str, where_vals, _} = sql_filter_param(mod, where, [], index)
		{"DELETE FROM " <> get_table(prefix, mod, opts) <> where_str <> sql, params ++ where_vals}
	end


	def insert_keys(struct) do
		"(" <> Enum.map_join(struct, ", ", &elem(&1, 0)) <> ")"
	end


	def insert_values(obj, acc \\ {nil, [], 1}) do
		{_mod, obj} = Map.pop(obj, @struct_field)
		{str, vals, index} = Enum.reduce(
			obj,
			acc,
			fn {k, v}, {str_acc, val_acc, index} = acc ->
				{append_str(str_acc, ",", "$#{index}"), [v | val_acc], index + 1}
			end
		)
		{IO.iodata_to_binary(str), vals, index}
	end


	# 全局表
	def get_table(nil, mod, _opts \\ []) do
		mod.get_table()
	end
	def get_table(prefix, mod, opts) when Kernel.is_struct(prefix) do
		get_table(UserLib.get_project_id(prefix), mod, opts)
	end
	def get_table(prefix, mod, opts) when is_binary(mod) do
		"#{prefix}.#{mod}"
		case opts[:alias] do
			nil ->
				"#{prefix}.#{mod}"
			v ->
				"#{prefix}.#{mod} AS #{v}"
		end
	end
	def get_table(prefix, mod, opts) do
		case {mod.is_global(), mod.get_table(), Keyword.get(opts, :alias)} do
			{false, table, nil} ->
				"#{inspect(prefix)}.#{table}"
			{false, table, alias} ->
				"#{inspect(prefix)}.#{table} AS #{alias}"
			{true, table, _} ->
				table
		end
	end
	def get_table2(prefix, mod, _opts \\ []) do
		"#{inspect(prefix)}.#{mod.get_table()}"
	end


	def convert("NULL") do
		"NULL"
	end
	def convert(v) when is_integer(v) do
		"#{v}"
	end
	def convert(v) when is_float(v) do
		"#{v}"
	end
	def convert(nil) do
		"'nil'"
	end
	def convert(val) when Kernel.is_map(val) do
		v = cond do
			Map.get(val, :__struct__, nil) == @time_type ->
				"#{val}"
			true ->
				Jason.encode!(val)
		end
		"'" <> v <> "'"
	end
	def convert(v) when is_list(v) do
		"'" <> list_convert(v) <> "'"
	end
	def convert(v) do
		"'#{v}'"
	end
	def convert_json_str(v) do
		"'\"#{v}\"'"
	end

	# 插入list 的时候用
	def list_convert(list) do
		"#{inspect(list)}"
		|> String.replace("[", "{")
		|> String.replace("]", "}")
	end

	# 判断值是否在list 里用
	def convert_in_list(list) do
		"#{inspect(list)}"
		|> String.replace("\"", "'")
		|> String.replace("[", "(")
		|> String.replace("]", ")")
	end
	def convert_in_json_list(list) do
		"(" <> Enum.map_join(list, ", ", &("'\"" <> &1 <> "\"'")) <> ")"
	end

	# 构造json 数组用 json array and array exists check
	def json_array(list) do
		"array[" <>
		Enum.map_join(
			list,
			", ",
			fn (h) ->
				"'#{inspect(h)}'"
			end
		) <>
		"]"
	end

	def to_date(ts \\ TimeLib.now_string()) do
		"to_timestamp(#{convert(ts)}, 'yyyy-MM-dd hh24:mi:ss')"
	end

	@doc """
	    list:[{"sql", str}]
	"""
	@spec sql_equal_param([{any, any}] | map, integer) :: {binary, [any], integer}
	def sql_equal_param(list, index \\ 1) do
		{str, val, index} =
			Enum.reduce(
				list,
				{nil, [], index},
				fn
					{oper, key, v}, {str_acc, val_acc, index_acc} ->
						{append_str(str_acc, ", ", "#{key}#{oper}#{index_acc}"), [v | val_acc], index_acc + 1}
					{"sql", val}, {str_acc, val_acc, index_acc} ->
						{append_str(str_acc, ", ", val), val_acc, index_acc}
					# func(values, {val_acc, index_acc, str_acc}) -> {val_acc, index_acc, str_acc}
					# str_acc: sql字符串
					{func, values}, {str_acc, val_acc, index_acc} when is_function(func) ->
						{val_acc, index_acc, vs} = func.(values, {val_acc, index_acc, []})
						{append_str(str_acc, ", ", vs), val_acc, index_acc}
					{key, val}, {str_acc, val_acc, index_acc} ->
						{append_str(str_acc, ", ", "#{key}=$#{index_acc}"), [val | val_acc], index_acc + 1}
					# func(element, {val_acc, index_acc, str_acc}) -> {val_acc, index_acc, str_acc}
					# str_acc: [sql_str]
					{"case", key, func, values}, {str_acc, val_acc, index_acc} when is_function(func) ->
						is_list(values) || is_map(values) ||
							PrivateCandy.debug_remind(__ENV__, "#{values}不是list or map")
						{val_acc, index_acc, vs} = Enum.reduce(values, {val_acc, index_acc, []}, &func.(&1, &2))
						{append_str(str_acc, ", ", "#{key}=case #{vs} end"), val_acc, index_acc}
				end
			)
		{IO.iodata_to_binary(str), val, index}
	end

	def sql_filter_param(mod, list, vals \\ [], index \\ 1) do
		fields = mod.fields()
		{str, val, index} =
			Enum.reduce(
				list,
				{nil, vals, index},
				fn
					{"or", func, values}, {str_acc, val_acc, index_acc} ->
						{val_acc, index_acc, vs} = Enum.reduce(values, {val_acc, index_acc, []}, &func.(&1, &2))
						{append_str(str_acc, " AND ", "(" <> Enum.join(vs, " OR ") <> ")"), val_acc, index_acc}
					{oper, key, val}, {str_acc, val_acc, index_acc} ->
						s = if is_list(val) do
							"any($#{index_acc})"
						else
							"$#{index_acc}"
						end
						{append_str(str_acc, " AND ", "#{key} #{oper} #{s}"), [val | val_acc], index_acc + 1}
					{"sql", sql}, {str_acc, val_acc, index_acc} ->
						{append_str(str_acc, " AND ", sql), val_acc, index_acc}
					{key, nil}, {str_acc, val_acc, index_acc} ->
						{append_str(str_acc, " AND ", "#{key} IS NULL"), val_acc, index_acc}
					{key, val}, {str_acc, val_acc, index_acc} ->
						val = DeeprConvert.lookup_convert(fields, key, val)
						s = case Map.get(fields, key) do
							%{type: type} when type in [:jsonb, :json] ->
								key <> " @> " <> "$#{index_acc}"
							%{type: {:array, _}} ->
								"$#{index_acc}=ANY(#{key})"
							_ ->
								key <> if is_list(val) do
									"=ANY($#{index_acc})"
								else
									"=$#{index_acc}"
								end
						end
						{append_str(str_acc, " AND ", s), [val | val_acc], index_acc + 1}
				end
			)
		if list == [] do
			{"", Enum.reverse(val), index}
		else
			{" WHERE " <> IO.iodata_to_binary(str), Enum.reverse(val), index}
		end
	end

	def append_str(nil, _joiner, h) do
		h
	end
	def append_str(acc, joiner, h) do
		[acc, joiner | h]
	end


	@doc """
	sql 和 return_json 互斥
	"""
	defp get_return_sql(opts) do
		sql = Keyword.get(opts, @sql)
		if sql == nil do
			case Keyword.get(opts, @return_json) do
				nil ->
					""
				list ->
					a = Enum.map_join(list, ",", &"'#{&1}',#{&1}")
					" returning json_build_object(#{a})"
			end
		else
			sql
		end
	end

	def calc_distance(:select, args, self_field, {lat_field, lon_field}) do
		case args do
			%{"lat" => lat_v, "lon" => lon_v} ->
				lat_v = Util.to_float(lat_v)
				lon_v = Util.to_float(lon_v)
				(is_number(lat_v) && is_number(lon_v)) || raise TipError, "lat_lon_invalid"
				"earth_distance(ll_to_earth(#{lat_field}, #{lon_field}), ll_to_earth(#{lat_v}, #{lon_v})) as #{self_field}"
			_ ->
				PrivateCandy.debug_remind(__ENV__, "lack lat-lon args")
		end
	end




end
